﻿using System;
using System.Collections.Generic;
using System.Linq;
using MySql.Data.MySqlClient;
using eStock.SQL;

namespace eStock.DataObjects
{
    public class TrayCollection : DataCollection<Tray>
    {
        public override Tray GetById(int id)
        {
            return List.Cast<Tray>().FirstOrDefault(c => c.Id == id);
        }

        public override void Replace(Tray newValue)
        {
            List[IndexOf(GetById(newValue.Id))] = newValue;
        }

        public Tray GetByComponentId(int cId)
        {
            return List.Cast<Tray>().FirstOrDefault(cs => cs.ComponentId == cId);
        }

        public override bool Update()
        {
            try
            {
                SQLDataReader res;
                foreach (Tray c in List)
                {
                     res = MainWindow.Database.QueryRead("SELECT * FROM es_tray WHERE id = " + c.Id);
                    int i = MainWindow.Database.FetchCount(res);
                    if (i == 0)
                    {
                        ((Tray)List[List.IndexOf(c)]).Id = Convert.ToInt32(MainWindow.Database.QueryWrite("INSERT INTO es_tray (componentId,  boxId,title, amount, price, condition, description) VALUES " +
                                                                                                     "('" + c.ComponentId + "',  '" + c.BoxId + "', '" + MainWindow.Database.EscapeString(c.Title) + "', '" + c.Amount + "', '" + c.Price + "', '" + c.Condition + "', '" + MainWindow.Database.EscapeString(c.Description) + "');"));
                    }
                    else
                    {
                        MainWindow.Database.QueryWrite("UPDATE es_tray SET componentId = '" + c.ComponentId + "', boxId = '" + c.BoxId +  "', title = '" + MainWindow.Database.EscapeString(c.Title) + "', amount = '" + c.Amount + "', price = '" + c.Price + "', condition = '" + c.Condition + "', description = '" + MainWindow.Database.EscapeString(c.Description) + "' WHERE id = " + c.Id + ";");
                    }

                    foreach (int shopId in c.ShopIds)
                    {
                        res = MainWindow.Database.QueryRead("SELECT * FROM es_trayShops WHERE stockId = " + c.Id + " AND shopId = " + shopId);
                        i = MainWindow.Database.FetchCount(res);
                        if (i == 0)
                        {
                            ((Tray)List[List.IndexOf(c)]).Id = Convert.ToInt32(MainWindow.Database.QueryWrite("INSERT INTO es_trayShops (shopId, stockId) VALUES " +
                                                                                                         "('" + shopId + "', '" + c.Id + "');"));
                        }
                        else
                        {
                            MainWindow.Database.QueryWrite("UPDATE es_trayShops SET shopId = '" + shopId + "', stockId = '" + c.Id + "' WHERE stockId = " + c.Id + " AND shopId = " + shopId + ";");
                        }
                    }
                }

                res = MainWindow.Database.QueryRead("SELECT * FROM es_tray");
                Dictionary<string, object> data = MainWindow.Database.FetchArray(res);
                List<int> removeIds = new List<int>();
                while (data != null)
                {
                    Tray c = GetById(Convert.ToInt32(data["id"]));
                    if (c == null)
                    {
                        removeIds.Add(Convert.ToInt32(data["id"]));
                    }
                    data = MainWindow.Database.FetchArray(res);
                }
                if (removeIds.Count > 0)
                {
                    string sqlString = "DELETE FROM es_tray WHERE ";
                    for (int j = 0; j < removeIds.Count; j++)
                    {
                        if (j != 0)
                            sqlString += " AND ";
                        sqlString += "id = " + removeIds[j];
                    }
                    MainWindow.Database.QueryWrite(sqlString);
                }

                return true;
            }
            catch (Exception)
            {
                return false;
            }
        }
    }
}
